package com.ts.common.constant;

/**
 * @Query相关的数据库常量
 */
public interface SQLConstant {

    String SQL_WHERE = " WHERE ";
    String SQL_FROM = " FROM ";
    String SQL_LEFT_BRACKET = " ( ";
    String SQL_RIGHT_BRACKET = " ) ";


    String SYSTEMROLEBYMENUURL = "  select sb.id,sb.button_name,sb.menuid,sb.button_remark,sb.button_id,sb.icon_cls,sb.create_time,sb.update_time ,sb.delete_flag,sb.is_publish,sb.belong_to,sb.user_id,sb.power_level  from sys_button sb LEFT JOIN sys_menu sm on sm.id = sb.menuid  where sm.menuurl=?1  ";

    String SYSTEMUSERLOCALUSERINFOSQL = "";


    String REPORT = " select temp.material_name as material ,temp.supplier_name as supplier ,temp.cars,temp.dialy, t2.allgross from " +
            " ( select t.material_name,t.supplier_name ,count(t.material_name) as cars, sum(suttle) dialy from ta_measure t " +
            " where t.move_type = 'IN' " +
            " and t.customer_name like ?1% " +
            " and Date(t.gross_weight_date)=?2 " +
            " group by t.supplier_name,t.material_name" +
            " order by t.material_name" +
            " ) temp , " +
            " (" +
            " select t.material_name,t.supplier_name ,sum(suttle) allgross from ta_measure t " +
            " where t.move_type = 'IN'" +
            " and t.customer_name like ?1%" +
            " group by t.supplier_name,t.material_name" +
            " order by t.material_name" +
            " ) t2" +
            " where temp.material_name = t2.material_name " +
            " and temp.supplier_name = t2.supplier_name";

    String INREPORT = " select  substring_index(tt.material_name,'|',1) as material,substring_index(tt.supplier_name,'|',1) as supplier,tt.cars,tt.dialy,CONCAT(tt.allgross,'(',tt.facount,')') allgross ,CONCAT(ta.daysuttle,'(',ta.daycount,')') as daysuttle, CONCAT(ta.allsuttle,'(',ta.allcount,')') as allsuttle from " +
            "(" +
            " select temp.material_name,temp.supplier_name,temp.cars,temp.dialy, t2.allgross ,t2.facount from" +
            " (" +
            " select t.material_name,t.supplier_name ,count(t.material_name) as cars, sum(suttle) dialy from ta_measure t " +
            " where t.move_type = 'IN'" +
            " and substring_index(t.customer_name,'|',1) = ?1" +
            " and t.delete_flag = 'N' " +
            " and t.suttle > 0 " +
            " and t.return_symbol = 0 " +
            " and Date(t.suttle_date) =?2" +
            " group by substring_index(t.supplier_name,'|',1),substring_index(t.material_name,'|',1)" +
            " order by substring_index(t.material_name,'|',1)" +
            " ) temp , " +
            " (" +
            " select t.material_name,t.supplier_name ,sum(suttle) allgross,COUNT(*) facount from ta_measure t " +
            " where t.move_type = 'IN'" +
            "  and t.suttle > 0 " +
            " and Date(t.suttle_date) >= ?3" +
            " and Date(t.suttle_date) <= ?4" +
            " and substring_index(t.customer_name,'|',1) = ?1" +
            " group by substring_index(t.supplier_name,'|',1),substring_index(t.material_name,'|',1)" +
            " order by substring_index(t.material_name,'|',1)" +
            " ) t2" +
            " where temp.material_name = t2.material_name " +
            " and temp.supplier_name = t2.supplier_name" +
            " ) tt  LEFT JOIN " +
            " (" +
            " (" +
            " select t1.material_name,t1.daysuttle,t1.daycount , t2.allsuttle,t2.allcount  from " +
            " (" +
            " SELECT t.material_name , sum(t.suttle) as daysuttle , COUNT(*) as daycount  from ta_measure t" +
            " where t.move_type = 'IN'" +
            " and substring_index(t.customer_name,'|',1) = ?1" +
            "  and t.suttle > 0 " +
            " and date(t.suttle_date) = ?2" +
            " group by substring_index(t.material_name,'|',1)" +
            " ) t1 ," +
            " (" +
            " SELECT t.material_name , sum(t.suttle) as allsuttle , COUNT(*) as allcount from ta_measure t" +
            " where t.move_type = 'IN'" +
            "  and t.suttle > 0 " +
            " and Date(t.suttle_date) >=?3 " +
            " and Date(t.suttle_date) <=?4 " +
            " and substring_index(t.customer_name,'|',1) = ?1" +
            " group by substring_index(t.material_name,'|',1)" +
            " ) t2" +
            " where substring_index(t1.material_name,'|',1) = substring_index(t2.material_name,'|',1)" +
            " ) ta" +
            " ) ON substring_index(tt.material_name,'|',1) = substring_index(ta.material_name,'|',1)";


    String YDINREPORT = "  select  substring_index(tt.material_name,'|',1) as material,substring_index(tt.supplier_name,'|',1) as supplier,tt.cars,tt.dialy,CONCAT(tt.allgross,'(',tt.facount,')') allgross ,CONCAT(ta.daysuttle,'(',ta.daycount,')') as daysuttle, CONCAT(ta.allsuttle,'(',ta.allcount,')') as allsuttle from " +
            "(" +
            " select temp.material_name,temp.supplier_name,temp.cars,temp.dialy, t2.allgross ,t2.facount from" +
            " (" +
            " select t.material_name,t.supplier_name ,count(t.material_name) as cars, sum(suttle) dialy from ta_measure t " +
            " where t.move_type = 'IN'" +
            " and substring_index(t.customer_name,'|',1) = ?1" +
            " and t.delete_flag = 'N' " +
            " and t.suttle > 0 " +
            " and t.return_symbol = 0 " +
            " and t.suttle_date >=?2" +
            " and t.suttle_date <=?5" +
            " group by substring_index(t.supplier_name,'|',1),substring_index(t.material_name,'|',1)" +
            " order by substring_index(t.material_name,'|',1)" +
            " ) temp , " +
            " (" +
            " select t.material_name,t.supplier_name ,sum(suttle) allgross,COUNT(*) facount from ta_measure t " +
            " where t.move_type = 'IN'" +
            "  and t.suttle > 0 " +
            " and Date(t.suttle_date) >= ?3" +
            " and Date(t.suttle_date) <= ?4" +
            " and substring_index(t.customer_name,'|',1) = ?1" +
            " group by substring_index(t.supplier_name,'|',1),substring_index(t.material_name,'|',1)" +
            " order by substring_index(t.material_name,'|',1)" +
            " ) t2" +
            " where temp.material_name = t2.material_name " +
            " and temp.supplier_name = t2.supplier_name" +
            " ) tt  LEFT JOIN " +
            " (" +
            " (" +
            " select t1.material_name,t1.daysuttle,t1.daycount , t2.allsuttle,t2.allcount  from " +
            " (" +
            " SELECT t.material_name , sum(t.suttle) as daysuttle , COUNT(*) as daycount  from ta_measure t" +
            " where t.move_type = 'IN'" +
            " and substring_index(t.customer_name,'|',1) = ?1" +
            "  and t.suttle > 0 " +
            " and t.suttle_date >= ?2" +
            " and t.suttle_date <= ?5" +
            " group by substring_index(t.material_name,'|',1)" +
            " ) t1 ," +
            " (" +
            " SELECT t.material_name , sum(t.suttle) as allsuttle , COUNT(*) as allcount from ta_measure t" +
            " where t.move_type = 'IN'" +
            "  and t.suttle > 0 " +
            " and Date(t.suttle_date) >=?3 " +
            " and Date(t.suttle_date) <=?4 " +
            " and substring_index(t.customer_name,'|',1) = ?1" +
            " group by substring_index(t.material_name,'|',1)" +
            " ) t2" +
            " where substring_index(t1.material_name,'|',1) = substring_index(t2.material_name,'|',1)" +
            " ) ta" +
            " ) ON substring_index(tt.material_name,'|',1) = substring_index(ta.material_name,'|',1)";

    String OUTREPORT = "  select  substring_index(tt.material_name,'|',1) as material,substring_index(tt.customer_name,'|',1) as supplier,tt.cars,tt.dialy,CONCAT(tt.allgross,'(',tt.facount,')') allgross,CONCAT(ta.daysuttle,'(',ta.daycount,')') as daysuttle,CONCAT(ta.allsuttle,'(',ta.allcount,')') as allsuttle from " +
            "(" +
            "             select temp.material_name,temp.customer_name,temp.cars,temp.dialy, t2.allgross,t2.facount  from" +
            "             (" +
            "             select t.material_name,t.customer_name ,count(t.material_name) as cars, sum(suttle) dialy from ta_measure t " +
            "             where t.move_type = 'OUT'" +
            "             and substring_index(t.supplier_name,'|',1) = ?1 " +
            "             and t.suttle > 0 " +
            "             and t.return_symbol = 0 " +
            "             and t.delete_flag = 'N'  " +
            "             and Date(t.suttle_date) =?2" +
            "             group by substring_index(t.customer_name,'|',1),substring_index(t.material_name,'|',1)" +
            "             order by substring_index(t.material_name,'|',1)" +
            "             ) temp , " +
            "             (" +
            "             select t.material_name,t.customer_name ,sum(suttle) allgross,COUNT(*) facount from ta_measure t " +
            "             where t.move_type = 'OUT' " +
            "             and t.suttle > 0 " +
            "             and Date(t.suttle_date) >=?3 " +
            "             and Date(t.suttle_date) <=?4 " +
            "             and substring_index(t.supplier_name,'|',1) = ?1 " +
            "             group by substring_index(t.customer_name,'|',1),substring_index(t.material_name,'|',1)" +
            "             order by substring_index(t.material_name,'|',1)" +
            "             ) t2" +
            "             where substring_index(temp.material_name,'|',1) = substring_index(t2.material_name,'|',1) " +
            "             and substring_index(temp.customer_name,'|',1) = substring_index(t2.customer_name,'|',1)" +
            "             ) tt  LEFT JOIN " +
            "             (" +
            "             (" +
            "             select t1.material_name,t1.daysuttle,t1.daycount ,t2.allsuttle ,t2.allcount from  " +
            "             (" +
            "             SELECT t.material_name , sum(t.suttle) AS daysuttle, COUNT(*) as daycount  from ta_measure t" +
            "             where t.move_type = 'OUT'" +
            "             and t.suttle > 0 " +
            "             and substring_index(t.supplier_name,'|',1) = ?1 " +
            "             and date(t.suttle_date) = ?2" +
            "             group by substring_index(t.material_name,'|',1)" +
            "             ) t1 ," +
            "             (" +
            "             SELECT t.material_name , sum(t.suttle) AS allsuttle ,  COUNT(*) as allcount  from ta_measure t" +
            "             where t.move_type = 'OUT'" +
            "             and t.suttle > 0 " +
            "             and substring_index(t.supplier_name,'|',1) = ?1 " +
            "             and Date(t.suttle_date) >= ?3" +
            "             and Date(t.suttle_date) <= ?4" +
            "             group by substring_index(t.material_name,'|',1)" +
            "             ) t2" +
            "             where substring_index(t1.material_name,'|',1) = substring_index(t2.material_name,'|',1)" +
            " ) ta" +
            "             ) ON substring_index(tt.material_name,'|',1) = substring_index(ta.material_name,'|',1)";

    String YDOUTREPORT = " select  substring_index(tt.material_name,'|',1) as material,substring_index(tt.customer_name,'|',1) as supplier,tt.cars,tt.dialy,CONCAT(tt.allgross,'(',tt.facount,')') allgross,CONCAT(ta.daysuttle,'(',ta.daycount,')') as daysuttle,CONCAT(ta.allsuttle,'(',ta.allcount,')') as allsuttle from " +
            "(" +
            "             select temp.material_name,temp.customer_name,temp.cars,temp.dialy, t2.allgross,t2.facount  from" +
            "             (" +
            "             select t.material_name,t.customer_name ,count(t.material_name) as cars, sum(suttle) dialy from ta_measure t " +
            "             where t.move_type = 'OUT'" +
            "             and substring_index(t.supplier_name,'|',1) = ?1 " +
            "             and t.suttle > 0 " +
            "             and t.return_symbol = 0 " +
            "             and t.delete_flag = 'N'  " +
            "             and t.suttle_date >=?2" +
            "             and t.suttle_date <=?5" +
            "             group by substring_index(t.customer_name,'|',1),substring_index(t.material_name,'|',1)" +
            "             order by substring_index(t.material_name,'|',1)" +
            "             ) temp , " +
            "             (" +
            "             select t.material_name,t.customer_name ,sum(suttle) allgross,COUNT(*) facount from ta_measure t " +
            "             where t.move_type = 'OUT' " +
            "             and t.suttle > 0 " +
            "             and Date(t.suttle_date) >=?3 " +
            "             and Date(t.suttle_date) <=?4 " +
            "             and substring_index(t.supplier_name,'|',1) = ?1 " +
            "             group by substring_index(t.customer_name,'|',1),substring_index(t.material_name,'|',1)" +
            "             order by substring_index(t.material_name,'|',1)" +
            "             ) t2" +
            "             where substring_index(temp.material_name,'|',1) = substring_index(t2.material_name,'|',1) " +
            "             and substring_index(temp.customer_name,'|',1) = substring_index(t2.customer_name,'|',1)" +
            "             ) tt  LEFT JOIN " +
            "             (" +
            "             (" +
            "             select t1.material_name,t1.daysuttle,t1.daycount ,t2.allsuttle ,t2.allcount from  " +
            "             (" +
            "             SELECT t.material_name , sum(t.suttle) AS daysuttle, COUNT(*) as daycount  from ta_measure t" +
            "             where t.move_type = 'OUT'" +
            "             and t.suttle > 0 " +
            "             and substring_index(t.supplier_name,'|',1) = ?1 " +
            "             and t.suttle_date >= ?2" +
            "             and t.suttle_date <= ?5" +
            "             group by substring_index(t.material_name,'|',1)" +
            "             ) t1 ," +
            "             (" +
            "             SELECT t.material_name , sum(t.suttle) AS allsuttle ,  COUNT(*) as allcount  from ta_measure t" +
            "             where t.move_type = 'OUT'" +
            "             and t.suttle > 0 " +
            "             and substring_index(t.supplier_name,'|',1) = ?1 " +
            "             and Date(t.suttle_date) >= ?3" +
            "             and Date(t.suttle_date) <= ?4" +
            "             group by substring_index(t.material_name,'|',1)" +
            "             ) t2" +
            "             where substring_index(t1.material_name,'|',1) = substring_index(t2.material_name,'|',1)" +
            " ) ta" +
            "             ) ON substring_index(tt.material_name,'|',1) = substring_index(ta.material_name,'|',1)";


    String zcb = "select  car.car_id as carId ,car.car_model as carModel ,car.axes_number as axesNumber,car.transport_unit as transportUnit ,car.transportpermit,car.driver,car.certificate,car.care_weight as careWeight,substring_index(measure.material_name,'|',1) as material ,measure.measure_id as measureId,measure.gross_weight as gross ,measure.tare,measure.gross_weight_date as grossDate,measure.tare_date as tareDate  from " +
            " (" +
            " select DISTINCT c.car_id,c.car_model,c.axes_number,c.transport_unit,c.transportpermit,c.driver,c.certificate,c.care_weight from ta_car c " +
            " ) car," +
            " (" +
            " select t.material_name,t.measure_id,t.gross_weight,t.tare,t.suttle,t.gross_weight_date,t.tare_date,t.move_type,t.car_id from ta_measure t " +
            "  where t.move_type = 'OUT' and t.delete_flag='N'   ) measure " +
            " where car.car_id = measure.car_id  and measure.gross_weight_date <> ''   ";

    String zcbCount = "select count(*) from " +
            " (" +
            " select DISTINCT c.car_id,c.car_model,c.axes_number,c.transport_unit,c.transportpermit,c.driver,c.certificate,c.care_weight from ta_car c " +
            " ) car," +
            " (" +
            " select t.material_name,t.measure_id,t.gross_weight,t.tare,t.suttle,t.gross_weight_date,t.tare_date,t.move_type,t.car_id from ta_measure t " +
            " where t.move_type = 'OUT' and t.delete_flag='N'  " +
            " ) measure " +
            " where car.car_id = measure.car_id   and measure.gross_weight_date <> ''  ";


    //    String totalReport = " select substring_index(t.material_name,'|',1),substring_index(t.supplier_name,'|',1),sum(t.gross_weight) gross,sum(t.tare) tare ,sum(t.suttle) suttle ,sum(t.suttle) ts ,count(*)  count  from ta_measure t " +
//            " where t.delete_flag = 'N' and t.suttle>0 and t.return_symbol=0 and t.move_type = 'IN' and DATE(t.tare_date) = 'TTT' " +
//            "  group by  t.material_name, t.supplier_name" +
//            " " +
//            " UNION " +
//            " " +
//            " select substring_index(t.material_name,'|',1),substring_index(t.customer_name,'|',1),sum(t.gross_weight) gross,sum(t.tare) tare ,sum(t.suttle) suttle ,sum(t.suttle) ts ,count(*)  count  from ta_measure t " +
//            " where t.delete_flag = 'N' and t.suttle>0 and t.return_symbol=0 and t.move_type = 'OUT' and DATE(t.gross_weight_date) = 'TTT' " +
//            " group by  t.material_name, t.customer_name ";

    //    String totalReport = "SELECT temp.material_name, temp.out_factory,temp.gross,temp.tare,temp.suttle,temp.ts,temp.count,temp.date " +
//            " FROM (" +
//            " SELECT " +
//            " substring_index(t.material_name, '|', 1) material_name, substring_index(t.supplier_name, '|', 1) out_factory, sum(t.gross_weight) gross,sum(t.tare) tare,sum(t.suttle) suttle,sum(t.suttle) ts,count(*) count,t.tare_date date " +
//            " FROM ta_measure t  WHERE " +
//            " t.delete_flag = 'N' AND t.suttle > 0 AND t.return_symbol = 0 AND t.move_type = 'IN' GROUP BY t.material_name,t.supplier_name " +
//            " UNION " +
//            " SELECT " +
//            " substring_index(t.material_name, '|', 1) material_name,substring_index(t.customer_name, '|', 1) out_factory,sum(t.gross_weight) gross,sum(t.tare) tare,sum(t.suttle) suttle,sum(t.suttle) ts,count(*) count,t.gross_weight_date date " +
//            " FROM " +
//            " ta_measure t WHERE " +
//            " t.delete_flag = 'N' AND t.suttle > 0 AND t.return_symbol = 0 AND t.move_type = 'OUT' GROUP BY t.material_name, t.customer_name " +
//            " ) temp " +
//            " WHERE  temp.date >= 'fromdate'  AND temp.date <= 'todate' ORDER BY temp.date";
    String totalReport = " SELECT  " +
            " substring_index(t.material_name, '|', 1),  " +
            " substring_index(t.customer_name, '|', 1), " +
            " sum(t.gross_weight) gross, " +
            " sum(t.tare) tare, " +
            " sum(t.suttle) suttle, " +
            " sum(t.suttle) ts, " +
            " count(*) count " +
            " FROM " +
            " ta_measure t " +
            " WHERE " +
            " t.delete_flag = 'N' " +
            " AND t.suttle > 0 " +
            " AND t.return_symbol = 0 " +
            " and t.move_type = 'OUT' " +
            " AND t.suttle_date >= 'fromdate' " +
            " AND t.suttle_date <= 'todate' " +
            " GROUP BY " +
            " substring_index(t.material_name,'|',1), " +
            " substring_index(t.customer_name,'|',1)" +
            " union " +
            " SELECT  " +
            " substring_index(t.material_name, '|', 1),  " +
            " substring_index(t.supplier_name, '|', 1), " +
            " sum(t.gross_weight) gross, " +
            " sum(t.tare) tare, " +
            " sum(t.suttle) suttle, " +
            " sum(t.suttle) ts, " +
            " count(*) count " +
            " FROM " +
            " ta_measure t " +
            " WHERE " +
            " t.delete_flag = 'N' " +
            " AND t.suttle > 0 " +
            " and t.move_type = 'IN'" +
            " AND t.return_symbol = 0 " +
            " AND t.suttle_date >= 'fromdate' " +
            " AND t.suttle_date <= 'todate' " +
            " GROUP BY " +
            " substring_index(t.material_name,'|',1), " +
            " substring_index(t.supplier_name,'|',1)";


    String INMONTH = "  select ta.material_name as mn , ta.supplier_name as sn,CONCAT(ROUND(ta.suttle,2),'(',ta.cout,')') as factotal ,CONCAT(ROUND(td.suttle,2),'(',td.cout,')') as mattotal ,tb.type,CONCAT(ROUND(tb.su,2),'(',tb.c,')') as cattotal   from    " +
            " (" +
            " SELECT " +
            " t.material_name, " +
            " t.supplier_name, " +
            " sum(t.suttle) as suttle, " +
            " count(*) as cout, " +
            " IF ( " +
            " t.material_name LIKE '%煤%'," +
            " '煤炭', " +
            " '其他' " +
            " ) AS type " +
            " FROM " +
            " ta_measure t " +
            " WHERE " +
            " t.move_type = 'IN' " +
            " AND t.customer_name = '##CUSTOMERNAME##' " +
            " and date(t.suttle_date) >= '##FROMDATE##' " +
            " and date(t.suttle_date) <= '##TODATE##' " +
            " and t.return_symbol =0 " +
            " and t.delete_flag = 'N' " +
            " GROUP BY " +
            " t.material_name, " +
            " t.supplier_name " +
            " order by type " +
            " ) ta " +
            " left join  " +
            " ( " +
            " select temp.type,sum(temp.suttle) as su,sum(temp.cout) as c from  " +
            " ( " +
            " " +
            " SELECT " +
            " sum(t.suttle) as suttle, " +
            " count(*) as cout, " +
            " IF ( " +
            " t.material_name LIKE '%煤%', " +
            " '煤炭', " +
            " '其他' " +
            " ) AS type " +
            " FROM " +
            " ta_measure t " +
            " WHERE " +
            " t.move_type = 'IN' " +
            " AND t.customer_name = '##CUSTOMERNAME##' " +
            " and date(t.suttle_date) >= '##FROMDATE##' " +
            " and date(t.suttle_date) <= '##TODATE##' " +
            " and t.return_symbol =0 " +
            " and t.delete_flag = 'N' " +
            " GROUP BY " +
            " t.material_name, " +
            " t.supplier_name " +
            " order by type " +
            " ) temp " +
            " group by temp.type " +
            " ) tb on ta.type = tb.type "+
            " LEFT JOIN"+
            " ("+
             " select tc.material,tc.suttle,tc.cout FROM "+
               " ( "+
                    "SELECT "+
                    " t.material_name as material, "+
                    " sum(t.suttle) as suttle, "+
                    " count(*) as cout "+
                    " FROM "+
                    " ta_measure t "+
                    " WHERE "+
                    " t.move_type = 'IN' "+
                    " AND t.customer_name = '##CUSTOMERNAME##' "+
                    " and date(t.suttle_date) >= '##FROMDATE##' "+
                    " and date(t.suttle_date) <= '##TODATE##' "+
                    " and t.return_symbol =0  "+
                    " and t.delete_flag = 'N' " +
                    " GROUP BY "+
                    " t.material_name "+
                    " ) tc "+
                    " ) td on ta.material_name = td.material  order by mn  ";

    String OUTMONTH = "  select ta.material_name as mn , ta.customer_name as sn,CONCAT(ROUND(ta.suttle,2),'(',ta.cout,')') as factotal ,CONCAT(ROUND(td.suttle,2),'(',td.cout,')') as mattotal ,tb.type,CONCAT(ROUND(tb.su,2),'(',tb.c,')') as cattotal   from    " +
            " (" +
            " SELECT " +
            " t.material_name, " +
            " t.customer_name, " +
            " sum(t.suttle) as suttle, " +
            " count(*) as cout, " +
            " IF ( " +
            " t.material_name LIKE '%煤%'," +
            " '煤炭', " +
            " '其他' " +
            " ) AS type " +
            " FROM " +
            " ta_measure t " +
            " WHERE " +
            " t.move_type = 'OUT' " +
            " AND t.supplier_name = '##CUSTOMERNAME##' " +
            " and date(t.suttle_date) >= '##FROMDATE##' " +
            " and date(t.suttle_date) <= '##TODATE##' " +
            " and t.return_symbol =0  " +
            " and t.delete_flag = 'N' " +
            " GROUP BY " +
            " t.material_name, " +
            " t.customer_name " +
            " order by type " +
            " ) ta " +
            " left join  " +
            " ( " +
            " select temp.type,sum(temp.suttle) as su,sum(temp.cout) as c from  " +
            " ( " +
            " " +
            " SELECT " +
            " sum(t.suttle) as suttle, " +
            " count(*) as cout, " +
            " IF ( " +
            " t.material_name LIKE '%煤%', " +
            " '煤炭', " +
            " '其他' " +
            " ) AS type " +
            " FROM " +
            " ta_measure t " +
            " WHERE " +
            " t.move_type = 'OUT' " +
            " AND t.supplier_name = '##CUSTOMERNAME##' " +
            " and date(t.suttle_date) >= '##FROMDATE##' " +
            " and date(t.suttle_date) <= '##TODATE##' " +
            " and t.return_symbol =0 " +
            " and t.delete_flag = 'N' " +
            " GROUP BY " +
            " t.material_name, " +
            " t.customer_name " +
            " order by type " +
            " ) temp " +
            " group by temp.type " +
            " ) tb on ta.type = tb.type "+
            " LEFT JOIN"+
            " ("+
            " select tc.material,tc.suttle,tc.cout FROM "+
            " ( "+
            "SELECT "+
            " t.material_name as material, "+
            " sum(t.suttle) as suttle, "+
            " count(*) as cout "+
            " FROM "+
            " ta_measure t "+
            " WHERE "+
            " t.move_type = 'OUT' "+
            " AND t.supplier_name = '##CUSTOMERNAME##' "+
            " and date(t.suttle_date) >= '##FROMDATE##' "+
            " and date(t.suttle_date) <= '##TODATE##' "+
            " and t.return_symbol =0 "+
            " and t.delete_flag = 'N' " +
            " GROUP BY "+
            " t.material_name "+
            " ) tc "+
            " ) td on ta.material_name = td.material  order by mn  ";


}
